{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## psycopg2のインストール\n",
    "\n",
    "PostgreSQLと連携する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting psycopg2\n",
      "  Downloading psycopg2-2.7.1-cp36-cp36m-manylinux1_x86_64.whl (2.7MB)\n",
      "Installing collected packages: psycopg2\n",
      "Successfully installed psycopg2-2.7.1\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "pip install psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'Taro', 21, 'Tokyo'), (2, 'Taro', 21, 'Tokyo'), (3, 'Jiro', 20, 'Osaka'), (4, 'Saburo', 19, 'Nagoya')]\n"
     ]
    }
   ],
   "source": [
    "### テスト\n",
    "import psycopg2 as pg\n",
    "with pg.connect(database='jsd_development',\n",
    "                user='jsd',\n",
    "                password='pass',\n",
    "                host='10.136.3.189',\n",
    "                port=5432) as conn, conn.cursor() as cur:\n",
    "  cur.execute( 'SELECT * FROM people' )\n",
    "  print( cur.fetchall() )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Taro</td>\n",
       "      <td>21</td>\n",
       "      <td>Tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Taro</td>\n",
       "      <td>21</td>\n",
       "      <td>Tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Jiro</td>\n",
       "      <td>20</td>\n",
       "      <td>Osaka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Saburo</td>\n",
       "      <td>19</td>\n",
       "      <td>Nagoya</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id    name  age address\n",
       "0   1    Taro   21   Tokyo\n",
       "1   2    Taro   21   Tokyo\n",
       "2   3    Jiro   20   Osaka\n",
       "3   4  Saburo   19  Nagoya"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### pandasテスト\n",
    "### http://www.mwsoft.jp/programming/numpy/rdb_to_pandas.html\n",
    "import pandas.io.sql as psql\n",
    "import psycopg2 as pg\n",
    "with pg.connect(database='jsd_development',\n",
    "                user='jsd',\n",
    "                password='pass',\n",
    "                host='10.136.3.189',\n",
    "                port=5432) as conn:\n",
    "  sql = 'SELECT * FROM people'\n",
    "  df = psql.read_sql(sql, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4 entries, 0 to 3\n",
      "Data columns (total 4 columns):\n",
      "id         4 non-null int64\n",
      "name       4 non-null object\n",
      "age        4 non-null int64\n",
      "address    4 non-null object\n",
      "dtypes: int64(2), object(2)\n",
      "memory usage: 208.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id          int64\n",
       "name       object\n",
       "age         int64\n",
       "address    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## mysqlclient(MySQLdb)のインストール\n",
    "\n",
    "MySQLと連携する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "読み込んだプラグイン:fastestmirror\n",
      "/var/tmp/yum-root-WryJ7H/mysql-community-release-el7-5.noarch.rpm を調べています: mysql-community-release-el7-5.noarch\n",
      "/var/tmp/yum-root-WryJ7H/mysql-community-release-el7-5.noarch.rpm: インストールされたパッケージを更新しません。\n",
      "読み込んだプラグイン:fastestmirror\n",
      "Loading mirror speeds from cached hostfile\n",
      " * base: ftp.riken.jp\n",
      " * epel: ftp.riken.jp\n",
      " * extras: ftp.riken.jp\n",
      " * updates: ftp.riken.jp\n",
      "パッケージ mysql-community-devel-5.6.37-2.el7.x86_64 はインストール済みか最新バージョンです\n",
      "何もしません\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "エラー: 何もしません\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "### MySQLのインストール(CentOS7)\n",
    "sudo yum install -y http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm\n",
    "sudo yum install -y mysql-community-devel.x86_64"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "# jpyter上から実行するとエラーになるがコンソールから実行するといける\n",
    "#sudo pip install mysqlclient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Tables_in_jorurimail\n",
      "0  ar_internal_metadata\n",
      "1          delayed_jobs\n",
      "2     schema_migrations\n",
      "3              sessions\n",
      "4             sys_files\n"
     ]
    }
   ],
   "source": [
    "import MySQLdb\n",
    "import pandas.io.sql as mysql\n",
    "\n",
    "### DB接続\n",
    "con = MySQLdb.connect(database='jorurimail', host='10.136.3.168', user='joruri', passwd='pass', charset='utf8')\n",
    "sql = \"show tables\"\n",
    "\n",
    "### pandasのDataFrameの形でデータを取り出す\n",
    "df = mysql.read_sql(sql, con) \n",
    "print (df.head(5))\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## sqlalchemyのインストール\n",
    "\n",
    "* PythonのO/Rマッパー\n",
    "    * readはMySQLdbだけでもできるが書き込みも行う場合は必要"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting sqlalchemy\n",
      "  Downloading SQLAlchemy-1.1.11.tar.gz (5.2MB)\n",
      "Building wheels for collected packages: sqlalchemy\n",
      "  Running setup.py bdist_wheel for sqlalchemy: started\n",
      "  Running setup.py bdist_wheel for sqlalchemy: finished with status 'done'\n",
      "  Stored in directory: /root/.cache/pip/wheels/ee/a7/64/0f699856164b752e1d398f9b7abc6c831074906fd10088932d\n",
      "Successfully built sqlalchemy\n",
      "Installing collected packages: sqlalchemy\n",
      "Successfully installed sqlalchemy-1.1.11\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "sudo pip install sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-07-21 12:08:28,305 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'\n",
      "2017-07-21 12:08:28,307 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-07-21 12:08:28,310 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()\n",
      "2017-07-21 12:08:28,313 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-07-21 12:08:28,316 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'\n",
      "2017-07-21 12:08:28,318 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-07-21 12:08:28,321 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1\n",
      "2017-07-21 12:08:28,324 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-07-21 12:08:28,326 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1\n",
      "2017-07-21 12:08:28,329 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-07-21 12:08:28,332 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1\n",
      "2017-07-21 12:08:28,333 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-07-21 12:08:28,337 INFO sqlalchemy.engine.base.Engine DROP TABLE zoo\n",
      "2017-07-21 12:08:28,338 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-07-21 12:08:28,342 INFO sqlalchemy.engine.base.Engine ROLLBACK\n"
     ]
    },
    {
     "ename": "OperationalError",
     "evalue": "(_mysql_exceptions.OperationalError) (1051, \"Unknown table 'test_db.zoo'\") [SQL: 'DROP TABLE zoo']",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1181\u001b[0m                         \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1182\u001b[0;31m                         context)\n\u001b[0m\u001b[1;32m   1183\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    469\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 470\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    471\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    249\u001b[0m             \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 250\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merrorhandler\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    251\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/connections.py\u001b[0m in \u001b[0;36mdefaulterrorhandler\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m     49\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrorvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mBaseException\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 50\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0merrorvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     51\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0merrorclass\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    246\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 247\u001b[0;31m             \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36m_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    410\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 411\u001b[0;31m         \u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    412\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_post_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36m_do_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    373\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_last_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 374\u001b[0;31m         \u001b[0mdb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    375\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/connections.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, query)\u001b[0m\n\u001b[1;32m    291\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 292\u001b[0;31m             \u001b[0m_mysql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    293\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: (1051, \"Unknown table 'test_db.zoo'\")",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-35-c662526d302f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mengine\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msa\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcreate_engine\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mecho\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'DROP TABLE zoo'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      7\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'CREATE TABLE zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT)'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[0;31m#engine.execute('CREATE TABLE IF NOT EXISTS zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT)')\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   2062\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2063\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcontextual_connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2064\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2065\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2066\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mscalar\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object, *multiparams, **params)\u001b[0m\n\u001b[1;32m    937\u001b[0m         \"\"\"\n\u001b[1;32m    938\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstring_types\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 939\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_text\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    940\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    941\u001b[0m             \u001b[0mmeth\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mobject\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_text\u001b[0;34m(self, statement, multiparams, params)\u001b[0m\n\u001b[1;32m   1095\u001b[0m             \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1096\u001b[0m             \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1097\u001b[0;31m             \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1098\u001b[0m         )\n\u001b[1;32m   1099\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1187\u001b[0m                 \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1188\u001b[0m                 \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1189\u001b[0;31m                 context)\n\u001b[0m\u001b[1;32m   1190\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1191\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1400\u001b[0m                 util.raise_from_cause(\n\u001b[1;32m   1401\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1402\u001b[0;31m                     \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1403\u001b[0m                 )\n\u001b[1;32m   1404\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    201\u001b[0m     \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    202\u001b[0m     \u001b[0mcause\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mexception\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 203\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcause\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    204\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    205\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mpy3k\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mreraise\u001b[0;34m(tp, value, tb, cause)\u001b[0m\n\u001b[1;32m    184\u001b[0m             \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__cause__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    185\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__traceback__\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 186\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    187\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    188\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1180\u001b[0m                         \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1181\u001b[0m                         \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1182\u001b[0;31m                         context)\n\u001b[0m\u001b[1;32m   1183\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1184\u001b[0m             self._handle_dbapi_exception(\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    468\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    469\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 470\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    471\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    472\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    249\u001b[0m             \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 250\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merrorhandler\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    251\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    252\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_defer_warnings\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/connections.py\u001b[0m in \u001b[0;36mdefaulterrorhandler\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m     48\u001b[0m     \u001b[0;32mdel\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     49\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrorvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mBaseException\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 50\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0merrorvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     51\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0merrorclass\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     52\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrorvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    245\u001b[0m         \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    246\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 247\u001b[0;31m             \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    249\u001b[0m             \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36m_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    409\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    410\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 411\u001b[0;31m         \u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    412\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_post_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    413\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36m_do_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    372\u001b[0m         \u001b[0mdb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_db\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    373\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_last_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 374\u001b[0;31m         \u001b[0mdb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    375\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    376\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/connections.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, query)\u001b[0m\n\u001b[1;32m    290\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_query_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    291\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 292\u001b[0;31m             \u001b[0m_mysql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    293\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    294\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__enter__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: (_mysql_exceptions.OperationalError) (1051, \"Unknown table 'test_db.zoo'\") [SQL: 'DROP TABLE zoo']"
     ]
    }
   ],
   "source": [
    "import sqlalchemy as sa\n",
    "\n",
    "url = 'mysql://jupyter:pass@10.136.3.168/test_db?charset=utf8'\n",
    "engine = sa.create_engine(url, echo=True)\n",
    "\n",
    "engine.execute('DROP TABLE zoo')\n",
    "engine.execute('CREATE TABLE zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT)')\n",
    "#engine.execute('CREATE TABLE IF NOT EXISTS zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT)')\n",
    "\n",
    "# SQL文に「?」が使用できないので、代わりに「%s」を使用\n",
    "ins = \"INSERT INTO zoo (critter, count, damages) VALUES (%s, %s, %s)\"\n",
    "engine.execute(ins, \"あひる\", 10, 0.0)\n",
    "engine.execute(ins, \"くま\", 2, 1000.0)\n",
    "engine.execute(ins, \"いたち\", 1, 2000.0)\n",
    "\n",
    "rows = engine.execute('SELECT * FROM zoo')\n",
    "\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PyYAMLのインストール\n",
    "\n",
    "YAMLを読み込む"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting PyYAML\n",
      "  Downloading PyYAML-3.12.tar.gz (253kB)\n",
      "Installing collected packages: PyYAML\n",
      "  Running setup.py install for PyYAML: started\n",
      "    Running setup.py install for PyYAML: finished with status 'done'\n",
      "Successfully installed PyYAML-3.12\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "pip install PyYAML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "development:\n",
      "  database: ftakao2007_development\n",
      "  username: ftakao2007\n",
      "  password: pass\n",
      "  port: 5432\n",
      "  host: 192.168.33.11\n",
      "\n",
      "production:\n",
      "  database: ftakao2007_production\n",
      "  username: ftakao2007\n",
      "  password: pass\n",
      "  port: 5432\n",
      "  host: 172.16.0.1\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "### ymlの中身確認\n",
    "import os\n",
    "\n",
    "f = open(\"yml/database.yml\", \"r\")\n",
    "for line in f:\n",
    "    print(line, end=\"\")\n",
    "print(\"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'development': {'database': 'ftakao2007_development', 'username': 'ftakao2007', 'password': 'pass', 'port': 5432, 'host': '192.168.33.11'}, 'production': {'database': 'ftakao2007_production', 'username': 'ftakao2007', 'password': 'pass', 'port': 5432, 'host': '172.16.0.1'}}\n",
      "{'database': 'ftakao2007_development', 'username': 'ftakao2007', 'password': 'pass', 'port': 5432, 'host': '192.168.33.11'}\n",
      "ftakao2007_development\n",
      "192.168.33.11\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'development': {'database': 'ftakao2007_development',\n",
       "  'host': '192.168.33.11',\n",
       "  'password': 'pass',\n",
       "  'port': 5432,\n",
       "  'username': 'ftakao2007'},\n",
       " 'production': {'database': 'ftakao2007_production',\n",
       "  'host': '172.16.0.1',\n",
       "  'password': 'pass',\n",
       "  'port': 5432,\n",
       "  'username': 'ftakao2007'}}"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### ymlの読み込み\n",
    "### http://qiita.com/ouiouiouioui/items/1d19aee73ff6e5135b73\n",
    "import yaml\n",
    "f = open(\"yml/database.yml\", \"r\")\n",
    "data = yaml.load(f)\n",
    "print(data)\n",
    "print(data['development'])\n",
    "print(data['development']['database'])\n",
    "print(data['development']['host'])\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>development</th>\n",
       "      <th>production</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>database</th>\n",
       "      <td>ftakao2007_development</td>\n",
       "      <td>ftakao2007_production</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>host</th>\n",
       "      <td>192.168.33.11</td>\n",
       "      <td>172.16.0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>password</th>\n",
       "      <td>pass</td>\n",
       "      <td>pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>port</th>\n",
       "      <td>5432</td>\n",
       "      <td>5432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>username</th>\n",
       "      <td>ftakao2007</td>\n",
       "      <td>ftakao2007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     development             production\n",
       "database  ftakao2007_development  ftakao2007_production\n",
       "host               192.168.33.11             172.16.0.1\n",
       "password                    pass                   pass\n",
       "port                        5432                   5432\n",
       "username              ftakao2007             ftakao2007"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### pandas連携テスト\n",
    "import pandas as pd\n",
    "pd.DataFrame.from_dict(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Shapelyのインストール\n",
    "\n",
    "* 地理空間データ上で様々な計算を行なう"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Reading package lists...\n",
      "Building dependency tree...\n",
      "Reading state information...\n",
      "The following additional packages will be installed:\n",
      "  libgeos-3.5.0 libgeos-c1v5\n",
      "Suggested packages:\n",
      "  libgdal-doc\n",
      "The following NEW packages will be installed:\n",
      "  libgeos-3.5.0 libgeos-c1v5 libgeos-dev\n",
      "0 upgraded, 3 newly installed, 0 to remove and 50 not upgraded.\n",
      "Need to get 596 kB of archives.\n",
      "After this operation, 2724 kB of additional disk space will be used.\n",
      "Get:1 http://archive.ubuntu.com/ubuntu xenial/universe amd64 libgeos-3.5.0 amd64 3.5.0-1ubuntu2 [477 kB]\n",
      "Get:2 http://archive.ubuntu.com/ubuntu xenial/universe amd64 libgeos-c1v5 amd64 3.5.0-1ubuntu2 [52.4 kB]\n",
      "Get:3 http://archive.ubuntu.com/ubuntu xenial/universe amd64 libgeos-dev amd64 3.5.0-1ubuntu2 [66.5 kB]\n",
      "Fetched 596 kB in 2s (260 kB/s)\n",
      "Selecting previously unselected package libgeos-3.5.0.\r\n",
      "(Reading database ... \r",
      "(Reading database ... 5%\r",
      "(Reading database ... 10%\r",
      "(Reading database ... 15%\r",
      "(Reading database ... 20%\r",
      "(Reading database ... 25%\r",
      "(Reading database ... 30%\r",
      "(Reading database ... 35%\r",
      "(Reading database ... 40%\r",
      "(Reading database ... 45%\r",
      "(Reading database ... 50%\r",
      "(Reading database ... 55%\r",
      "(Reading database ... 60%\r",
      "(Reading database ... 65%\r",
      "(Reading database ... 70%\r",
      "(Reading database ... 75%\r",
      "(Reading database ... 80%\r",
      "(Reading database ... 85%\r",
      "(Reading database ... 90%\r",
      "(Reading database ... 95%\r",
      "(Reading database ... 100%\r",
      "(Reading database ... 46084 files and directories currently installed.)\r\n",
      "Preparing to unpack .../libgeos-3.5.0_3.5.0-1ubuntu2_amd64.deb ...\r\n",
      "Unpacking libgeos-3.5.0 (3.5.0-1ubuntu2) ...\r\n",
      "Selecting previously unselected package libgeos-c1v5.\r\n",
      "Preparing to unpack .../libgeos-c1v5_3.5.0-1ubuntu2_amd64.deb ...\r\n",
      "Unpacking libgeos-c1v5 (3.5.0-1ubuntu2) ...\r\n",
      "Selecting previously unselected package libgeos-dev.\r\n",
      "Preparing to unpack .../libgeos-dev_3.5.0-1ubuntu2_amd64.deb ...\r\n",
      "Unpacking libgeos-dev (3.5.0-1ubuntu2) ...\r\n",
      "Processing triggers for libc-bin (2.23-0ubuntu7) ...\r\n",
      "Setting up libgeos-3.5.0 (3.5.0-1ubuntu2) ...\r\n",
      "Setting up libgeos-c1v5 (3.5.0-1ubuntu2) ...\r\n",
      "Setting up libgeos-dev (3.5.0-1ubuntu2) ...\r\n",
      "Processing triggers for libc-bin (2.23-0ubuntu7) ...\r\n",
      "Collecting shapely\n",
      "  Using cached Shapely-1.5.17.tar.gz\n",
      "Installing collected packages: shapely\n",
      "  Running setup.py install for shapely: started\n",
      "    Running setup.py install for shapely: finished with status 'done'\n",
      "Successfully installed shapely-1.5.17\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "debconf: delaying package configuration, since apt-utils is not installed\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "apt-get install -y libgeos-dev\n",
    "pip install shapely"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0101000080000000000000F03F00000000000000400000000000000840\n"
     ]
    }
   ],
   "source": [
    "### テスト\n",
    "from shapely import geos, wkb, wkt\n",
    "\n",
    "# Show EWKB\n",
    "p = wkt.loads('POINT Z(1 2 3)')\n",
    "print(p.wkb_hex)  # This is little endian EWKB with just PointZ bit set"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## geojsonのインストール\n",
    "\n",
    "* GeoJSONを扱うためのライブラリ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting geojson\n",
      "  Downloading geojson-1.3.5-py2.py3-none-any.whl\n",
      "Installing collected packages: geojson\n",
      "Successfully installed geojson-1.3.5\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "pip install geojson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{\"coordinates\": [-115.81, 37.24], \"type\": \"Point\"}"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### テスト\n",
    "from geojson import Point\n",
    "Point((-115.81, 37.24))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{\"coordinates\": [[-155.52, 19.61], [-156.22, 20.74], [-157.97, 21.46]], \"type\": \"MultiPoint\"}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from geojson import MultiPoint\n",
    "MultiPoint([(-155.52, 19.61), (-156.22, 20.74), (-157.97, 21.46)]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pillowのインストール\n",
    "\n",
    "* Pillow\n",
    "    * Python の画像処理ライブラリ\n",
    "    * PIL(Python2.7まで)のforkプロジェクト\n",
    "        * 3系対応\n",
    "    * 参考\n",
    "        * [Python 3.5 対応画像処理ライブラリ Pillow (PIL) の使い方](https://librabuch.jp/blog/2013/05/python_pillow_pil/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pillow\n",
      "  Downloading Pillow-4.2.1-cp36-cp36m-manylinux1_x86_64.whl (5.8MB)\n",
      "Collecting olefile (from pillow)\n",
      "  Downloading olefile-0.44.zip (74kB)\n",
      "Installing collected packages: olefile, pillow\n",
      "  Running setup.py install for olefile: started\n",
      "    Running setup.py install for olefile: finished with status 'done'\n",
      "Successfully installed olefile-0.44 pillow-4.2.1\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "pip install pillow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/root/python/jupyter_notebook/Python'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "### カレントディレクトリの確認\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "### テスト(テキストを画像で出力)\n",
    "from PIL import Image, ImageDraw, ImageFont\n",
    "\n",
    "# 画像オブジェクトを作成。サイズと背景色を指定する。背景色はRBGの各々をtupleにして与える。\n",
    "text_canvas = Image.new('RGB', (80, 40), (255, 255, 255))\n",
    "draw = ImageDraw.Draw(text_canvas)\n",
    "\n",
    "# フォントの種類とサイズを指定\n",
    "#font = ImageFont.truetype('/Library/Fonts/ipag.ttf', 15)\n",
    "font = ImageFont.truetype('/root/.fonts/ipag.ttf', 15)\n",
    "\n",
    "# テキストを書き込み。引数は順に、書き込み座標（tuple）、テキスト、テキストのフォント、テキストのカラー\n",
    "draw.text((10, 10), 'hogehoge', font=font, fill='#000')\n",
    "\n",
    "# 保存\n",
    "text_canvas.save('img/text_img.jpg', 'JPEG', quality=100, optimize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "テスト画像の表示\n",
    "\n",
    "<img src=\"img/text_img.jpg\" alt=\"テスト画像の表示\" title=\"テスト画像の表示\" align=\"left\" />  \n",
    "\n",
    "<br clear=\"left\">"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
